package com.songhq.data.controller;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.songhq.data.service.TeamService;

@RestController
@RequestMapping("/team")
public class NbaTeamAndPlayerController {
	
	@Autowired
	private TeamService temaService;
	
	
	@RequestMapping("/save")
	public String saveTeamNba() throws Exception {

		
		Map result = temaService.selectTeams();
		
		List<Map> teams = (List<Map>)result.get("teams");
		
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet();

		HSSFRow headerRow = sheet.createRow(0);
		
		HSSFCell cell0 = headerRow.createCell(0);
		cell0.setCellValue("conferenceid");
		
		HSSFCell cell1 = headerRow.createCell(1);
		cell1.setCellValue("teamid");
		
		HSSFCell cell2 = headerRow.createCell(2);
		cell2.setCellValue("teammarket");
		
		HSSFCell cell3 = headerRow.createCell(3);
		cell3.setCellValue("venuename");
		
		HSSFCell cell4 = headerRow.createCell(4);
		cell4.setCellValue("divisionalias");
		
		HSSFCell cell5 = headerRow.createCell(5);
		cell5.setCellValue("teambeitainame");
		
		HSSFCell cell6 = headerRow.createCell(6);
		cell6.setCellValue("founded");
		
		HSSFCell cell7 = headerRow.createCell(7);
		cell7.setCellValue("season");
		
		HSSFCell cell8 = headerRow.createCell(8);
		cell8.setCellValue("teamalias");
		
		HSSFCell cell9 = headerRow.createCell(9);
		cell9.setCellValue("teamname");
		
		HSSFCell cell10 = headerRow.createCell(10);
		cell10.setCellValue("coachname");
		
		HSSFCell cell11 = headerRow.createCell(11);
		cell11.setCellValue("teamnameen");
		
		HSSFCell cell12 = headerRow.createCell(12);
		cell12.setCellValue("venueid");
		
		HSSFCell cell13 = headerRow.createCell(13);
		cell13.setCellValue("venuebeitainame");
		
		
		HSSFCell cell14 = headerRow.createCell(14);
		cell14.setCellValue("conferencealias");
		
		HSSFCell cell15 = headerRow.createCell(15);
		cell15.setCellValue("leagueid");
		
		HSSFCell cell16 = headerRow.createCell(16);
		cell16.setCellValue("divisionid");

		for(int i=0; i< teams.size();i++) {
			Map map = teams.get(i);
			HSSFRow createRow = sheet.createRow(i+1);
			HSSFCell Cell0 = createRow.createCell(0);
			HSSFCell Cell1 = createRow.createCell(1);
			HSSFCell Cell2 = createRow.createCell(2);
			HSSFCell Cell3 = createRow.createCell(3);
			HSSFCell Cell4 = createRow.createCell(4);
			HSSFCell Cell5 = createRow.createCell(5);
			HSSFCell Cell6 = createRow.createCell(6);
			HSSFCell Cell7 = createRow.createCell(7);
			HSSFCell Cell8 = createRow.createCell(8);
			HSSFCell Cell9 = createRow.createCell(9);
			HSSFCell Cell10 = createRow.createCell(10);
			HSSFCell Cell11 = createRow.createCell(11);
			HSSFCell Cell12 = createRow.createCell(12);
			HSSFCell Cell13 = createRow.createCell(13);
			HSSFCell Cell14 = createRow.createCell(14);
			HSSFCell Cell15 = createRow.createCell(15);
			HSSFCell Cell16 = createRow.createCell(16);
			Cell0.setCellValue((String)map.get("conferenceid"));
			Cell1.setCellValue((String)map.get("teamid"));
			Cell2.setCellValue((String)map.get("teammarket"));
			Cell3.setCellValue((String)map.get("venuename"));
			Cell4.setCellValue((String)map.get("divisionalias"));
			Cell5.setCellValue((String)map.get("teambeitainame"));
			Cell6.setCellValue((String)map.get("founded"));
			Cell7.setCellValue((String)map.get("season"));
			Cell8.setCellValue((String)map.get("teamalias"));
			Cell9.setCellValue((String)map.get("teamname"));
			Cell10.setCellValue((String)map.get("coachname"));
			Cell11.setCellValue((String)map.get("teamnameen"));
			Cell12.setCellValue((String)map.get("venueid"));
			Cell13.setCellValue((String)map.get("venuebeitainame"));
			Cell14.setCellValue((String)map.get("conferencealias"));
			Cell15.setCellValue((String)map.get("leagueid"));
			Cell16.setCellValue((String)map.get("divisionid"));
		}
		
		FileOutputStream out = new FileOutputStream("nba球队表.xls");

		workbook.write(out);
		return "seccess";
	}
	

	@RequestMapping("/player/save")
	public String savePlayerNba() throws Exception {
		
		int rowIndex = 1;
		Map result = temaService.selectTeams();
		
		List<Map> teams = (List<Map>)result.get("teams");
		
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		HSSFSheet sheet = workbook.createSheet();

		HSSFRow headerRow = sheet.createRow(0);
		
		HSSFCell cell0 = headerRow.createCell(0);
		cell0.setCellValue("position");
		
		HSSFCell cell1 = headerRow.createCell(1);
		cell1.setCellValue("playerstatus");
		
		HSSFCell cell2 = headerRow.createCell(2);
		cell2.setCellValue("weight");
		
		HSSFCell cell3 = headerRow.createCell(3);
		cell3.setCellValue("college");
		
		HSSFCell cell4= headerRow.createCell(4);
		cell4.setCellValue("draftteamid");
		
		HSSFCell cell5= headerRow.createCell(5);
		cell5.setCellValue("lastname");
		
		HSSFCell cell6 = headerRow.createCell(6);
		cell6.setCellValue("fullnameen");
		
		HSSFCell cell7 = headerRow.createCell(7);
		cell7.setCellValue("id");
		
		HSSFCell cell8 = headerRow.createCell(8);
		cell8.setCellValue("height");
		
		HSSFCell cell9 = headerRow.createCell(9);
		cell9.setCellValue("abbrname");
		
		HSSFCell cell10 = headerRow.createCell(10);
		cell10.setCellValue("age");
		
		HSSFCell cell11 = headerRow.createCell(11);
		cell11.setCellValue("primaryposition");
		
		HSSFCell cell12 = headerRow.createCell(12);
		cell12.setCellValue("birthdate");
		
		HSSFCell cell13 = headerRow.createCell(13);
		cell13.setCellValue("experience");
		
		HSSFCell cell14 = headerRow.createCell(14);
		cell14.setCellValue("weightkg");
		
		HSSFCell cell15 = headerRow.createCell(15);
		cell15.setCellValue("fullname");
		
		
		HSSFCell cell16 = headerRow.createCell(16);
		cell16.setCellValue("jerseynumber");
		
		HSSFCell cell17 = headerRow.createCell(17);
		cell17.setCellValue("draftpick");
		
		HSSFCell cell18 = headerRow.createCell(18);
		cell18.setCellValue("firstnameen");
		
		HSSFCell cell19 = headerRow.createCell(19);
		cell19.setCellValue("teamid");
		
		HSSFCell cell20 = headerRow.createCell(20);
		cell20.setCellValue("draftyear");
		
		HSSFCell cell21 = headerRow.createCell(21);
		cell21.setCellValue("heightcm");
		
		HSSFCell cell22 = headerRow.createCell(22);
		cell22.setCellValue("playerid");
		
		HSSFCell cell23 = headerRow.createCell(23);
		cell23.setCellValue("firstname");
		
		HSSFCell cell24 = headerRow.createCell(24);
		cell24.setCellValue("lastnameen");
		
		HSSFCell cell25 = headerRow.createCell(25);
		cell25.setCellValue("season");
		
		HSSFCell cell26 = headerRow.createCell(26);
		cell26.setCellValue("teamname");
		
		HSSFCell cell27 = headerRow.createCell(27);
		cell27.setCellValue("birthplace");
		
		HSSFCell cell28 = headerRow.createCell(28);
		cell28.setCellValue("leagueid");
		
		HSSFCell cell29 = headerRow.createCell(29);
		cell29.setCellValue("draftround");

		
		for(int i=0; i< teams.size();i++) {
			String teamid = (String) teams.get(i).get("teamid");
			Map result2 = temaService.selectPlayers(teamid);
			List<Map> players = (List<Map>) result2.get("players");
			for(int j=0; j<players.size();j++) {
				Map map = players.get(j);
				HSSFRow createRow = sheet.createRow(rowIndex);
				HSSFCell Cell0 = createRow.createCell(0);
				HSSFCell Cell1 = createRow.createCell(1);
				HSSFCell Cell2 = createRow.createCell(2);
				HSSFCell Cell3 = createRow.createCell(3);
				HSSFCell Cell4 = createRow.createCell(4);
				HSSFCell Cell5 = createRow.createCell(5);
				HSSFCell Cell6 = createRow.createCell(6);
				HSSFCell Cell7 = createRow.createCell(7);
				HSSFCell Cell8 = createRow.createCell(8);
				HSSFCell Cell9 = createRow.createCell(9);
				HSSFCell Cell10 = createRow.createCell(10);
				HSSFCell Cell11 = createRow.createCell(11);
				HSSFCell Cell12 = createRow.createCell(12);
				HSSFCell Cell13 = createRow.createCell(13);
				HSSFCell Cell14 = createRow.createCell(14);
				HSSFCell Cell15 = createRow.createCell(15);
				HSSFCell Cell16 = createRow.createCell(16);
				HSSFCell Cell17 = createRow.createCell(17);
				HSSFCell Cell18 = createRow.createCell(18);
				HSSFCell Cell19 = createRow.createCell(19);
				HSSFCell Cell20 = createRow.createCell(20);
				HSSFCell Cell21 = createRow.createCell(21);
				HSSFCell Cell22 = createRow.createCell(22);
				HSSFCell Cell23 = createRow.createCell(23);
				HSSFCell Cell24 = createRow.createCell(24);
				HSSFCell Cell25 = createRow.createCell(25);
				HSSFCell Cell26 = createRow.createCell(26);
				HSSFCell Cell27 = createRow.createCell(27);
				HSSFCell Cell28 = createRow.createCell(28);
				HSSFCell Cell29 = createRow.createCell(29);
				Cell0.setCellValue((String)map.get("position"));
				Cell1.setCellValue((String)map.get("playerstatus"));
				Cell2.setCellValue((String)map.get("weight"));
				Cell3.setCellValue((String)map.get("college"));
				Cell4.setCellValue((String)map.get("draftteamid"));
				Cell5.setCellValue((String)map.get("lastname"));
				Cell6.setCellValue((String)map.get("fullnameen"));
				Cell7.setCellValue((int)map.get("id"));
				Cell8.setCellValue((String)map.get("height"));
				Cell9.setCellValue((String)map.get("abbrname"));
				Cell10.setCellValue((int)map.get("age"));
				Cell11.setCellValue((String)map.get("primaryposition"));
				Cell12.setCellValue((String)map.get("birthdate"));
				Cell13.setCellValue((String)map.get("experience"));
				Cell14.setCellValue((double)map.get("weightkg"));
				Cell15.setCellValue((String)map.get("fullname"));
				Cell16.setCellValue((String)map.get("jerseynumber"));
				Cell17.setCellValue((String)map.get("draftpick"));
				Cell18.setCellValue((String)map.get("firstnameen"));
				Cell19.setCellValue((String)map.get("teamid"));
				Cell20.setCellValue((String)map.get("draftyear"));
				Cell21.setCellValue((double)map.get("heightcm"));
				Cell22.setCellValue((String)map.get("playerid"));
				Cell23.setCellValue((String)map.get("firstname"));
				Cell24.setCellValue((String)map.get("lastnameen"));
				Cell25.setCellValue((String)map.get("season"));
				Cell26.setCellValue((String)map.get("teamname"));
				Cell27.setCellValue((String)map.get("birthplace"));
				Cell28.setCellValue((String)map.get("leagueid"));
				Cell29.setCellValue((String)map.get("draftround"));
				rowIndex++;
			}
		}
		FileOutputStream out = new FileOutputStream("nba球员表.xls");
		workbook.write(out);
		return "seccess";
	}
	
	
	
	

}
